package com.alibaba.wallstreet.uitls.excel;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.alibaba.wallstreet.base.ExcelSheet;
import com.alibaba.wallstreet.base.ExcelWorkBook;
import com.alibaba.wallstreet.exception.ExcelParseErrorCode;
import com.alibaba.wallstreet.exception.ExcelParseException;

/**
 * 类ExcelUtil.java的实现描述：excel工具类，导出格式为xlsx，导入格式xlsx
 * 
 * @author wb-wangshuwen 2014年6月4日 下午7:42:36
 */
public class ExcelUtil {

    @SuppressWarnings({ "unchecked", "rawtypes" })
    public void exportExcel(ExcelSheet excelSheet, OutputStream out) throws Exception {
        // 声明一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        createSheet(excelSheet, workbook);
        workbook.write(out);
    }

    /**
     * 创建第一行为报表标题的sheet
     * 
     * @param excelSheet
     * @param out
     * @throws Exception
     */
    @SuppressWarnings({ "unchecked", "rawtypes" })
    public void exportExcelWithSheetTitle(ExcelSheet excelSheet, OutputStream out) throws Exception {
        // 声明一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        createSheetWithSheetTitle(excelSheet, workbook);
        workbook.write(out);
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
    public void exportExcel(List<ExcelSheet> excelSheets, OutputStream out) throws Exception {
        // 声明一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        for (ExcelSheet excelSheet : excelSheets) {
            createSheet(excelSheet, workbook);
        }
        workbook.write(out);
    }

    /**
     * 创建第一行为报表标题的sheet
     * 
     * @param excelSheets
     * @param out
     * @throws Exception
     */
    @SuppressWarnings({ "unchecked", "rawtypes" })
    public void exportExcelWithSheetsTitle(List<ExcelSheet> excelSheets, OutputStream out) throws Exception {
        // 声明一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        for (ExcelSheet excelSheet : excelSheets) {
            createSheetWithSheetTitle(excelSheet, workbook);
        }
        workbook.write(out);
    }

    private void createSheet(ExcelSheet mySheet, SXSSFWorkbook workbook) {

        // 生成一个表格
        Sheet sheet = workbook.createSheet(mySheet.getTitle());
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 15);
        // 是否需要Style？
        // CellStyle style = getDefaultStyle(workbook);
        // 产生表格标题行
        createDefaultHeaders(sheet, mySheet.getHeaders());

        int index = 1;
        for (List<Object> list : mySheet.getData()) {
            createLine(index, sheet, list);
            index++;
        }
    }

    /**
     * 创建第一行为报表标题的sheet
     * 
     * @param mySheet
     * @param workbook
     */
    private void createSheetWithSheetTitle(ExcelSheet mySheet, SXSSFWorkbook workbook) {
        // 生成一个表格
        Sheet sheet = workbook.createSheet(mySheet.getTitle());
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 15);
        CellStyle style = setSheetTitleStyle(workbook);
        CellStyle border = setCellBorderStyle(workbook);
        // 产生表格标题行
        createDefaultHeadersWithTitle(sheet, mySheet.getHeaders(), mySheet.getTitle(), style);

        int index = 2;
        for (List<Object> list : mySheet.getData()) {
            createLineWithBorder(index, sheet, list, border);
            index++;
        }
    }

    private void createLine(int index, Sheet sheet, List<Object> list) {
        Row row = sheet.createRow(index);
        for (int i = 0; i < list.size(); i++) {
            Cell cell = row.createCell(i);
            String textValue = getStringValueByObject(list.get(i));
            if (textValue != null) {
                cell.setCellValue(textValue);
            } else {
                cell.setCellValue("");
            }
        }
    }

    /**
     * 带边框的单元格
     * 
     * @param index
     * @param sheet
     * @param list
     */
    private void createLineWithBorder(int index, Sheet sheet, List<Object> list, CellStyle borderStyle) {
        Row row = sheet.createRow(index);
        for (int i = 0; i < list.size(); i++) {
            Cell cell = row.createCell(i);
            String textValue = getStringValueByObject(list.get(i));
            if (textValue != null) {
                cell.setCellValue(textValue);
            } else {
                cell.setCellValue("");
            }
            cell.setCellStyle(borderStyle);
        }
    }

    private void createDefaultHeaders(Sheet sheet, List<String> headers) {
        Row row = sheet.createRow(0);
        for (int i = 0; i < headers.size(); i++) {
            Cell cell = row.createCell(i);
            XSSFRichTextString text = new XSSFRichTextString(headers.get(i));
            cell.setCellValue(text);
        }
    }

    /**
     * 创建第一行为报表标题的sheet
     * 
     * @param sheet
     * @param headers
     * @param title
     */
    private void createDefaultHeadersWithTitle(Sheet sheet, List<String> headers, String title, CellStyle style) {
        sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) headers.size() - 1));
        Row row = sheet.createRow(0);
        Cell cellTitle = row.createCell(0);
        cellTitle.setCellValue(title);
        cellTitle.setCellStyle(style);
        Row row2 = sheet.createRow(1);
        for (int i = 0; i < headers.size(); i++) {
            Cell cell = row2.createCell(i);
            XSSFRichTextString text = new XSSFRichTextString(headers.get(i));
            cell.setCellValue(text);
            cell.setCellStyle(style);
        }
    }

    private CellStyle getDefaultStyle(SXSSFWorkbook workbook) {
        // 生成一个样式
        CellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 生成一个字体
        Font font = workbook.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
        return style;
    }

    /**
     * sheet第一行标题样式
     * 
     * @param workbook
     * @return
     */
    private CellStyle setSheetTitleStyle(SXSSFWorkbook workbook) {
        CellStyle center = workbook.createCellStyle();
        center.setAlignment(CellStyle.ALIGN_CENTER);
        Font bold = workbook.createFont();
        bold.setBoldweight(Font.BOLDWEIGHT_BOLD);
        center.setFont(bold);
        center.setBorderBottom(CellStyle.BORDER_THIN);
        center.setBorderLeft(CellStyle.BORDER_THIN);
        center.setBorderRight(CellStyle.BORDER_THIN);
        center.setBorderTop(CellStyle.BORDER_THIN);
        return center;
    }

    /**
     * 边框样式
     * 
     * @param workbook
     * @return
     */
    private CellStyle setCellBorderStyle(SXSSFWorkbook workbook) {
        CellStyle border = workbook.createCellStyle();
        border.setBorderBottom(CellStyle.BORDER_THIN);
        border.setBorderLeft(CellStyle.BORDER_THIN);
        border.setBorderRight(CellStyle.BORDER_THIN);
        border.setBorderTop(CellStyle.BORDER_THIN);
        return border;
    }

    private static String getStringValueByObject(Object value) {
        if (value == null) {
            return null;
        }
        String textValue = null;
        if (value instanceof Boolean) {
            if (Boolean.FALSE.equals(value)) {
                return "N";
            } else {
                return "Y";
            }
        } else if (value instanceof Date) {
            Date date = (Date) value;
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            textValue = sdf.format(date);
        } else if (value instanceof byte[]) {
        } else {
            // 其它数据类型都当作字符串简单处理
            textValue = value.toString();
        }
        return textValue;
    }

    /**
     * 根据输入流获取ExcelWorkBook
     * @param is
     * @return
     * @throws ExcelParseException
     * @author wb-xiaoyi
     */
    public static ExcelWorkBook getExcelWorkBook(InputStream is) {
        ExcelWorkBook excelWorkBook = new ExcelWorkBook();
        try {
            XSSFWorkbook workbook = getWorkBook(is);
            Sheet sheet = getSheet(workbook);
            
            ExcelSheet excelSheet = new ExcelSheet();
            excelSheet.setData(getExcelData(sheet));
            
            excelWorkBook.setSheet(excelSheet);
        } catch (ExcelParseException e) {
            throw e;
        } catch (IOException e) {
            throw new ExcelParseException(ExcelParseErrorCode.EXCEL_PARSE_ERROR.getDescription(),
                    ExcelParseErrorCode.EXCEL_PARSE_ERROR);
        }
        return excelWorkBook;
    }
    
    /**
     * 根据输入流获取ExcelSheet
     * @param is
     * @return
     * @throws ExcelParseException
     * @author wb-xiaoyi
     */
    public static ExcelSheet getExcelSheet(InputStream is) {
        ExcelSheet excelSheet = new ExcelSheet();
        try {
            XSSFWorkbook workbook = getWorkBook(is);
            Sheet sheet = getSheet(workbook);
            excelSheet.setData(getExcelData(sheet));
        } catch (ExcelParseException e) {
            throw e;
        } catch (IOException e) {
            throw new ExcelParseException(ExcelParseErrorCode.EXCEL_PARSE_ERROR.getDescription(),
                    ExcelParseErrorCode.EXCEL_PARSE_ERROR);
        }
        return excelSheet;
    }

    /**
     * 根据输入流获取工作薄
     * @param is
     * @return
     * @throws IOException
     * @author wb-xiaoyi
     */
    public static XSSFWorkbook getWorkBook(InputStream is) throws IOException{
        XSSFWorkbook workbook = new XSSFWorkbook(is);
        return workbook;
    }

    /**
     * 获取工作薄中的第一个工作表
     * @param workbook
     * @return
     * @author wb-xiaoyi
     */
    public static Sheet getSheet(XSSFWorkbook workbook) {
        return workbook.getSheetAt(0);
    }

    /**
     * 获取工作表中的数据
     * @param sheet
     * @return
     * @throws ExcelParseException 
     * @author wb-xiaoyi
     */
    public static List<List<Object>> getExcelData(Sheet sheet) throws ExcelParseException {
        List<List<Object>> result = new ArrayList<List<Object>>();
        
        Iterator<Row> iteratorRow = sheet.rowIterator();
        // 遍历每行
        while (iteratorRow.hasNext()) {
            Row row = iteratorRow.next();
            // 跳过第一行表头
            if (row.getRowNum() == 0) {
                continue;
            }
            // 解决无数据行包含空字符串单元格的情况，设置空行标识符
            boolean isBlankRow = true;
            
            List<Object> datas = new ArrayList<Object>();
            
            // 遍历每列
            for (int j = 0; j < row.getLastCellNum(); j ++) {
                Cell cell = row.getCell(j);
                if (cell != null) {
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING : // 字符串
                        String dataString = cell.getStringCellValue();
                        if (StringUtils.isBlank(dataString)) {
                            datas.add(null);
                        } else {
                            datas.add(dataString);
                            isBlankRow = false;
                        }
                        break;
                    case Cell.CELL_TYPE_NUMERIC : //数字
                        double dataNumeric = cell.getNumericCellValue();
                        datas.add(dataNumeric);
                        isBlankRow = false;
                        break;
                    case Cell.CELL_TYPE_BLANK :
                        datas.add(null);
                        break;
                    default :
                        int errorRowNumber = row.getRowNum() + 1;
                        int errorColNumber = cell.getColumnIndex() + 1;
                        throw new ExcelParseException("第" + errorRowNumber + "行，第" + errorColNumber + "列："
                                + ExcelParseErrorCode.EXCEL_CELL_FORMAT_NOT_ALLOW.getDescription(),
                                ExcelParseErrorCode.EXCEL_CELL_FORMAT_NOT_ALLOW);
                    }
                } else {
                    datas.add(null);
                }
            }

            if (!isBlankRow) {
                result.add(datas);
            }
        }
        return result;
    }
}
